package com.finple;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DBConnection {
	Connection con;
	Statement stmt;
	ResultSet rs;
	PreparedStatement pstmt;

	// 생성자로 드라이버를 로딩한다.
	public DBConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("드라이버 로딩 실패!");
			System.out.println(e);
		}
		
		setConnection();
	}

	/**
	 * DB Connection 관련 메서드
	 */
	// 싱글톤 객체 생성
	protected static DBConnection m_pInst;

	public static DBConnection GetInst() {
		if (m_pInst == null) {
			m_pInst = new DBConnection();
		}

		return m_pInst;
	}

	// DB에 접속한다.
	public void setConnection() {
		try {
			String url = "jdbc:mysql://203.237.183.109/mp";
			String ID = "root";
			String PW = "ailab";

			con = DriverManager.getConnection(url, ID, PW);
			// System.out.println("DB접속 성공!");
		} catch (SQLException e) {
			System.out.println("DB접속 실패!");
			e.printStackTrace();
		}
	}

	// 자원 반환 & 접속 종료
	public void endConnection() {
		try {
			rs.close();
			stmt.close();
			// pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 미팅 데이터를 넣는다.
	public void insertMeetingData(MeetingInfoVo info) {
		try {
			String sql = "insert into meeting(m_title,m_password,m_location,m_description,m_createtime,m_initiatorAlias,m_initiatorEmail,poll_time,poll_id) "
					+ "values(?,?,?,?,now(),?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			System.out.println(info.toString());
			pstmt.setString(1, info.getTitle());
			pstmt.setString(2, info.getPassword());
			pstmt.setString(3, info.getLocation());
			pstmt.setString(4, info.getDescription());
			pstmt.setString(5, info.getInitiatorAlias());
			pstmt.setString(6, info.getInitiatorEmail());
			pstmt.setString(7, info.getPollTime());
			pstmt.setString(8, info.getPollId());

			if (pstmt.executeUpdate() == 0) {
				System.out.println("실패!");
			}

			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public MeetingInfoVo getMeetingData(String pollId) {
		MeetingInfoVo info = null;

		try {
			stmt = con.createStatement();
			String sql = "select * from meeting where poll_id='" + pollId + "';";
			rs = stmt.executeQuery(sql);

			if (rs.next()) {
				info = new MeetingInfoVo(rs.getString("m_title"), rs.getString("m_password"),
						rs.getString("m_location"), rs.getString("m_description"), rs.getString("m_createtime"),
						rs.getString("m_initiatorAlias"), rs.getString("m_initiatorEmail"), rs.getString("poll_time"),
						rs.getString("poll_id"));
				System.out.println(info.toString());
			}
			else {
				System.out.println("what?");
			}
			endConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return info;
	}
	
	public MeetingInfoVo getMeetingData() {
		MeetingInfoVo info = null;

		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("select * from meeting");

			if (rs.getRow() > 0) {
				info = new MeetingInfoVo(rs.getString("m_title"), rs.getString("m_password"),
						rs.getString("m_location"), rs.getString("m_description"), rs.getString("m_createtime"),
						rs.getString("m_initiatorAlias"), rs.getString("m_initiatorEmail"), rs.getString("poll_time"),
						rs.getString("poll_id"));
			}
			endConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return info;
	}

	public ArrayList<String> getTimeData(String pollId) {
		ArrayList<String> timeInfo = new ArrayList<String>();

		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT DATE_FORMAT(start_date, '%Y-%m-%d %H:%i') as start_date, DATE_FORMAT(end_date, '%Y-%m-%d %H:%i') as end_date FROM events order by start_date;");
			while (rs.next()) {
				String timeStr = rs.getString("start_date") + " - <br/>" + rs.getString("end_date"); 
				timeInfo.add(timeStr);
			}

			endConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return timeInfo;
	}
}
